package com.cqh.dao.impl;

import com.cqh.bean.Student;
import com.cqh.dao.DBUtils;
import com.cqh.dao.StudentDao;
import com.cqh.util.StudentEnum;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {
    @Override
    public List<Student> getAllStudents(String stuname,String stuno,int sex,int pageIndex,int pageSize) {
        Student student = null;
        List list = new ArrayList();
        List params = new ArrayList();
        StringBuffer sqlBuffer = new StringBuffer(" select * from student where 1=1 and state!=4 ");
        if (stuname!=null && stuname.length()>0){
            sqlBuffer.append("and stuname like ? ");
            params.add("%" + stuname + "%");
        }
        if (stuno!=null && stuno.length()>0){
            sqlBuffer.append("and stuno=? ");
            params.add(stuno);
        }
        if (sex != -1){
            sqlBuffer.append("and sex=? ");
            params.add(sex);
        }

        //分页
        //  limit   (pageIndex-1)*pageSize,pageSize
        sqlBuffer.append(" limit ?,? "); //1 5   limit 0,5
        params.add((pageIndex-1)*pageSize);
        params.add(pageSize);
        try {
            ResultSet resultSet = query(sqlBuffer.toString(), params);
            while (resultSet.next()){
                student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setState(resultSet.getInt("state"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setEmail(resultSet.getString("email"));
                student.setAddress(resultSet.getString("address"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setIntroduction(resultSet.getString("introduction"));
                list.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }

    @Override
    public int total(String stuname, String stuno, int sex) {
        int total = 0;
        List params = new ArrayList();
        StringBuffer sqlBuffer = new StringBuffer(" select count(*) from student where 1=1 and state!=4 ");
        if (stuname!=null && stuname.length()>0){
            sqlBuffer.append("and stuname like ? ");
            params.add("%" + stuname + "%");
        }
        if (stuno!=null && stuno.length()>0){
            sqlBuffer.append("and stuno=? ");
            params.add(stuno);
        }
        if (sex != -1){
            sqlBuffer.append("and sex=? ");
            params.add(sex);
        }
        try {
            ResultSet query = query(sqlBuffer.toString(), params);
            while (query.next()){
                total = query.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public int insertStudent(Student student) {
        int i = 0;
        String sql = "insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        List params = new ArrayList();
        try {
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(new Date());
            params.add(StudentEnum.READING.type);
            params.add(student.getIntroduction());
            params.add(student.getGid());
            i = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return i;
    }

    @Override
    public Student findById(int stuId) {
        Student student = new Student();
        String sql = "select * from student where stuid=?";
        List params = new ArrayList();
        params.add(stuId);
        try {
            ResultSet query = query(sql, params);
            while (query.next()){
                student.setStuId(query.getInt("stuid"));
                student.setStuNo(query.getString("stuno"));
                student.setStuName(query.getString("stuname"));
                student.setSex(query.getInt("sex"));
                student.setState(query.getInt("state"));
                student.setPhone(query.getString("phone"));
                student.setProfession(query.getString("profession"));
                student.setRegistered(query.getString("registered"));
                student.setRegDate(query.getDate("regdate"));
                student.setEmail(query.getString("email"));
                student.setAddress(query.getString("address"));
                student.setIdNumber(query.getString("idnumber"));
                student.setPolitics(query.getString("politics"));
                student.setIntroduction(query.getString("introduction"));
                student.setGid(query.getInt("gid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

    @Override
    public int updateStu(Student student) {
        int update = 0;
        String sql = "update student set stuno=?,stuname=?,gid=?,sex=?,email=?,phone=?,registered=?,address=?,politics=?,idnumber=?,profession=?,introduction=? where stuid=?";
        try {
            List params = new ArrayList();
            params.add(student.getStuNo());
            params.add(student.getStuName());
            params.add(student.getGid());
            params.add(student.getSex());
            params.add(student.getEmail());
            params.add(student.getPhone());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getPolitics());
            params.add(student.getIdNumber());
            params.add(student.getProfession());
            params.add(student.getIntroduction());
            params.add(student.getStuId());
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public int delStu(String stuId) {
        int update = 0;
        String sql = "update student set state=? where stuid=?";
        List params = new ArrayList();
        try {
            params.add(StudentEnum.DELETE.type);
            params.add(stuId);
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }
}
